﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Win32;
using OfficeOpenXml;
using YJTTool.Core;
using LicenseContext = OfficeOpenXml.LicenseContext;

namespace YJTTool.Common.Helper
{
    public class ExcelHelper : InheritBase
    {
        private static readonly Lazy<ExcelHelper> _instance = new Lazy<ExcelHelper>(() => new ExcelHelper());
        private ExcelHelper() { }

        public static ExcelHelper Instance => _instance.Value;

        // <summary>
        /// 打开文件选择对话框并导入 Excel 文件
        /// </summary>
        /// <returns>导入的文件路径，如果用户取消选择则返回 null</returns>

        public string ImportExcelFile()
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            var openFileDialog = new OpenFileDialog
            {
                Filter = "Excel Files|*.xls;*.xlsx", // 设置文件类型过滤器
                Title = "选择 Excel 文件",           // 设置对话框标题
                Multiselect = false                  // 禁止选择多个文件
            };

            // 显示文件选择对话框
            if (openFileDialog.ShowDialog() == true)
            {
                string filePath = openFileDialog.FileName;
                if (File.Exists(filePath))
                {
                    try
                    {
                        return filePath;
                    }
                    catch (Exception ex)
                    {
                        MessageError($"文件读取失败: {ex.Message}");
                    }
                }
                else
                {
                    MessageError($"文件不存在");
                }
            }

            return null; // 用户取消选择
        }
        public string ExportExcelFile()
        {
            SaveFileDialog saveFileDialog = new SaveFileDialog
            {
                Filter = "SQL Files (*.sql)|*.sql|All Files (*.*)|*.*",
                Title = "Save SQL File"
            };
            if (saveFileDialog.ShowDialog() == true)
            {
                // 获取用户选择的文件路径
                string filePath = saveFileDialog.FileName;
                return filePath;
            }
            else
            {
                MessageInfo("文件保存操作被取消。");
                return null;
            }
        }

        /// <summary>
        /// 读取 Excel 文件内容
        /// </summary>
        /// <param name="filePath">Excel 文件路径</param>
        private void ReadExcelFile(string filePath)
        {
            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                var workbook = package.Workbook;
                if (workbook.Worksheets.Count == 0)
                {
                    MessageWarning("Excel 文件中没有工作表");
                    return;
                }

                // 读取第一个工作表
                var worksheet = workbook.Worksheets[0];
                int rowCount = worksheet.Dimension.Rows;
                int colCount = worksheet.Dimension.Columns;

                // 遍历每一行和每一列
                for (int row = 1; row <= rowCount; row++)
                {
                    for (int col = 1; col <= colCount; col++)
                    {
                        var cellValue = worksheet.Cells[row, col].Text;
                        Console.WriteLine($"行 {row}, 列 {col}: {cellValue}");
                    }
                }

                MessageInfo($"成功读取 Excel 文件: {filePath}");
            }
        }

        /// <summary>
        /// 获取 Excel 文件中指定工作表和列的数据
        /// </summary>
        /// <param name="filePath">Excel 文件路径</param>
        /// <param name="sheetName">工作表名称</param>
        /// <param name="columnIndex">指定的列索引，从1开始</param>
        /// <returns>返回指定列的数据列表</returns>
        public List<string> GetColumnData(string filePath, string sheetName, string columnstr)
        {
            int columnIndex = ColumnLetterToIndex(columnstr);
            List<string> columnData = new List<string>();

            using (var package = new ExcelPackage(new FileInfo(filePath)))
            {
                var workbook = package.Workbook;
                if (workbook.Worksheets.Count == 0)
                {
                    MessageWarning("Excel 文件中没有工作表");
                    return columnData;
                }

                // 获取指定名称的工作表
                var worksheet = workbook.Worksheets[sheetName];
                if (worksheet == null)
                {
                    MessageWarning($"未找到名为 {sheetName} 的工作表");
                    return columnData;
                }

                int rowCount = worksheet.Dimension.Rows;

                // 遍历每一行的指定列
                for (int row = 1; row <= rowCount; row++)
                {
                    var cellValue = worksheet.Cells[row, columnIndex].Text; // 获取指定列的数据
                    columnData.Add(cellValue);
                }

                MessageInfo($"成功获取工作表 {sheetName} 中第 {columnIndex} 列的数据");
            }

            return columnData;
        }

        /// <summary>
        /// 将 WPS 或 Excel 中的列字母转换为对应的列索引（1-based）
        /// </summary>
        /// <param name="columnLetter">列字母，如 "A", "B", "Z", "AA" 等</param>
        /// <returns>返回对应的列索引</returns>
        public int ColumnLetterToIndex(string columnLetter)
        {
            int columnIndex = 0;

            // 遍历列字母的每一位
            foreach (char c in columnLetter.ToUpper())
            {
                // 将每个字母转换为对应的数字并更新列索引
                columnIndex = columnIndex * 26 + (c - 'A' + 1);
            }

            return columnIndex;
        }

        /// <summary>
        /// 将数据和标题写入 Excel 文件
        /// </summary>
        /// <param name="filePath">Excel 文件的路径</param>
        /// <param name="headers">标题行</param>
        /// <param name="data">要写入的数据（每个字符串数组表示一行数据）</param>
        public void WriteDataToExcel(string filePath, string[] headers, List<string[]> data,string Sheet)
        {
            // 确保 EPPlus 使用非商业许可证
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

            // 打开现有的 Excel 文件
            FileInfo fileInfo = new FileInfo(filePath);

            if (!fileInfo.Exists)
            {
                Console.WriteLine("文件不存在！");
                return;
            }

            // 创建新的 Excel 文件
            using (var package = new ExcelPackage(fileInfo))
            {
                // 创建一个工作表
                var worksheet = package.Workbook.Worksheets.Add(Sheet);

                // 写入标题行
                for (int colIndex = 0; colIndex < headers.Length; colIndex++)
                {
                    worksheet.Cells[1, colIndex + 1].Value = headers[colIndex];
                }

                // 将数据写入到工作表，从第二行开始
                for (int rowIndex = 0; rowIndex < data.Count; rowIndex++)
                {
                    for (int colIndex = 0; colIndex < data[rowIndex].Length; colIndex++)
                    {
                        worksheet.Cells[rowIndex + 2, colIndex + 1].Value = data[rowIndex][colIndex]; // 从第二行开始写入数据
                    }
                }

                // 保存 Excel 文件
                package.Save();
            }

            Console.WriteLine("标题和数据写入成功！");
        }
    }
}
